﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.Models;
using SWDataDB.DAL;
using System.Data.SqlClient;

namespace SWDataDB.DAL
{
    //钻孔图片操作
    public class ZKImageInfo
    {
        /// <summary>
        /// 获取ZK_IMAGE_TBL中所有记录的条数
        /// </summary>
        /// <returns></returns>
        public long GetZKImageInfoCount()
        {
            long lCount = 0;
            string strSql = "SELECT COUNT(*) AS count FROM dbo.ZK_IMAGE_TBL";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            DataRow dr = dt.Rows[0];
            lCount = long.Parse(dr["count"].ToString());

            return lCount;
        }

        /// <summary>
        /// 根据档号和钻孔编号获取记录条数
        /// </summary>
        /// <returns></returns>
        public int GetZKImageInfoCount(string zl_file_id, string zk_code)
        {
            int lCount = 0;
            string strSql = "SELECT COUNT(*) AS count FROM dbo.ZK_IMAGE_TBL WHERE ZL_FILE_ID = '" + zl_file_id + " ' AND ZK_CODE = '" + zk_code + " '";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            DataRow dr = dt.Rows[0];
            lCount = int.Parse(dr["count"].ToString());

            return lCount;
        }

        /// <summary>
        /// 根据档号、钻孔名称和回次获取记录数
        /// </summary>
        /// <param name="zl_file_id"></param>
        /// <param name="zk_code"></param>
        /// <returns></returns>
        public int GetZKImageInfoCount(string zl_file_id, string zk_code,int hc)
        {
            int lCount = 0;
            string strSql = "SELECT COUNT(*) AS count FROM dbo.ZK_IMAGE_TBL WHERE ZL_FILE_ID = '" + zl_file_id + " ' AND ZK_CODE = '" + zk_code + " ' AND HC = " + hc.ToString();
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            DataRow dr = dt.Rows[0];
            lCount = int.Parse(dr["count"].ToString());

            return lCount;
        }

        /// <summary>
        /// 该钻孔是否存在
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public bool IsZKImageExist(string file_id,string zk_code)
        {
            string strSql = "SELECT COUNT(*) AS COUNT FROM dbo.ZK_IMAGE_TBL WHERE FILE_ID='" + file_id + "' AND ZK_CODE='" + zk_code + "'";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            DataRow dr = dt.Rows[0];
            if (int.Parse(dr["COUNT"].ToString()) > 0)
            {
                return true;
            }
            else return false;
        }

        /// <summary>
        /// 得到指定档号、指定钻孔编号、指定回次的所有图片名称
        /// </summary>
        /// <returns>返回dataset</returns>
        public DataSet GetPicNameWithSet(string zl_file_id,string zk_code,int hc)
        {
            string strSql = "SELECT * FROM dbo.ZK_IMAGE_TBL WHERE ZL_FILE_ID = '"+zl_file_id+"' AND ZK_CODE = '"+zk_code+"' AND HC = "+hc.ToString();
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到指定档号、指定钻孔编号、指定回次的记录信息
        /// </summary>
        /// <param name="zl_file_id"></param>
        /// <param name="zk_code"></param>
        /// <param name="hc"></param>
        /// <returns></returns>
        public DataSet GetPicAllInfoWithSet(string zl_file_id, string zk_code, int hc)
        {
            string strSql = "SELECT * FROM dbo.ZK_IMAGE_TBL WHERE ZL_FILE_ID = '" + zl_file_id + "' AND ZK_CODE = '" + zk_code + "' AND HC = " + hc.ToString();
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到指定档号、指定钻孔编号所有图片记录
        /// </summary>
        /// <returns>返回dataset</returns>
        public DataSet GetAllPicWithSet(string zl_file_id, string zk_code)
        {
            string strSql = "SELECT * FROM dbo.ZK_IMAGE_TBL WHERE ZL_FILE_ID = '" + zl_file_id + "' AND ZK_CODE = '" + zk_code + " '";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 根据著录表档号和钻孔名称得到所有图片信息
        /// </summary>
        /// <param name="zl_file_id"></param>
        /// <param name="zk_code"></param>
        /// <returns></returns>
        public DataSet GetAllInfoWithSet(string zl_file_id,string zk_code)
        {
            string strSql = "SELECT * FROM dbo.ZK_IMAGE_TBL WHERE ZL_FILE_ID = '" + zl_file_id + "' AND ZK_CODE = '" + zk_code + "'";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        //根据主键ID取回对应的图片
        public DataSet GetInfoByID(int zkid)
        {
            string strSql = "SELECT * FROM dbo.ZK_IMAGE_TBL WHERE ID = " + zkid;
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        #region 将excel中的数据存储至sql

        /// <summary>
        /// 添加TBL数据至SQL
        /// </summary>
        /// <param name="zl_info"></param>
        /// <returns></returns>
        public int AddZKImageInfo(ZK_IMAGE_TBL zkImg_info)
        {
            // 先处理普通数据
            string strSql = "INSERT INTO dbo.ZK_IMAGE_TBL(ZL_FILE_ID,ZK_CODE,YX_LENGTH,START_JD,END_JD,";
            strSql += "SUM_HC,HC,HC_START_DEEP,HC_END_DEEP,PS_NUM,PIC_FILE_NAME,PIC_START_DEEP,";
            strSql += "PIC_END_DEEP,BMP_FILE_NAME) VALUES(";

            StringBuilder sbSql = new StringBuilder(strSql);

            strSql += "'" + zkImg_info.O_ZL_FILE_ID + "',";
            strSql += "'" + zkImg_info.O_ZK_CODE + "',";
            strSql += "'" + zkImg_info.O_YX_LENGTH.ToString() + "',";
            strSql += "'" + zkImg_info.O_START_JD.ToString() + "',";
            strSql += "'" + zkImg_info.O_END_JD.ToString() + "',";
            strSql += "'" + zkImg_info.O_SUM_HC.ToString() + "',";
            strSql += "'" + zkImg_info.O_HC.ToString() + "',";
            strSql += "'" + zkImg_info.O_HC_START_DEEP.ToString() + "',";
            strSql += "'" + zkImg_info.O_HC_END_DEEP.ToString() + "',";
            strSql += "'" + zkImg_info.O_PS_NUM.ToString() + "',";
            strSql += "'" + zkImg_info.O_PIC_FILE_NAME + "',";
            strSql += "'" + zkImg_info.O_PIC_START_DEEP.ToString() + "',";
            strSql += "'" + zkImg_info.O_PIC_END_DEEP.ToString() + "',";
            strSql += "'" + zkImg_info.O_BMP_FILE_NAME + "')";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 将DataSet存至SQL
        /// </summary>
        /// <param name="excelDS"></param>
        /// <returns></returns>
        public int AddDataSet2SQL(DataSet excelDS)
        {
            int nRow = excelDS.Tables[0].Rows.Count;
            for (int i = 0; i < nRow; i++)
            {
                ZK_IMAGE_TBL zkImgTbl = new ZK_IMAGE_TBL();
                zkImgTbl.O_ZL_FILE_ID = excelDS.Tables[0].Rows[2][0].ToString();
                zkImgTbl.O_ZK_CODE = excelDS.Tables[0].Rows[2][2].ToString();
                zkImgTbl.O_YX_LENGTH = float.Parse(excelDS.Tables[0].Rows[2][3].ToString());
                zkImgTbl.O_START_JD = float.Parse(excelDS.Tables[0].Rows[2][4].ToString());
                zkImgTbl.O_END_JD = float.Parse(excelDS.Tables[0].Rows[2][5].ToString());
                zkImgTbl.O_SUM_HC = int.Parse(excelDS.Tables[0].Rows[2][6].ToString());
                zkImgTbl.O_HC = int.Parse(excelDS.Tables[0].Rows[i+6][0].ToString());
                zkImgTbl.O_HC_START_DEEP = float.Parse(excelDS.Tables[0].Rows[i + 6][1].ToString());
                zkImgTbl.O_HC_END_DEEP = float.Parse(excelDS.Tables[0].Rows[i + 6][2].ToString());
                zkImgTbl.O_PS_NUM = int.Parse(excelDS.Tables[0].Rows[i + 6][3].ToString());

                zkImgTbl.O_PIC_FILE_NAME = excelDS.Tables[0].Rows[i + 6][4].ToString();

                zkImgTbl.O_PIC_START_DEEP = float.Parse(excelDS.Tables[0].Rows[i + 6][5].ToString());
                zkImgTbl.O_PIC_END_DEEP = float.Parse(excelDS.Tables[0].Rows[i + 6][6].ToString());

                zkImgTbl.O_BMP_FILE_NAME = excelDS.Tables[0].Rows[i + 6][4].ToString();

                if (AddZKImageInfo(zkImgTbl) != 1)
                {
                    return 0;
                }
            }

            return 1;
        }

        /// <summary>
        /// 通过excel路径名，将excel信息存储至SQL数据库
        /// </summary>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public int AddExcel2SQL(string excelPath)
        {
            ExcelReader er = new ExcelReader();

            DataSet ds = new DataSet();

            ds = er.ConvertExcelToDataTable(excelPath);

            if (AddDataSet2SQL(ds) != 1)
            {
                return 0;
            }

            return 1;
        }

        #endregion

        /// <summary>
        /// 通过ID删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteZKImgInfoByID(int id)
        {
            string strSql = "DELETE FROM dbo.ZK_IMAGE_TBL WHERE ID ='";
            strSql += id + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 根据档号和钻孔号删除著录表数据
        /// </summary>
        /// <param name="file_id">著录表档号</param>
        /// <returns></returns>
        public int DeleteZLInfo(string file_id,string zk_code)
        {
            string strSql = "DELETE FROM dbo.ZL_TBL WHERE FILE_ID ='" + file_id + "' AND ZK_CODE = '" + zk_code + "' ";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 更新，需要知道记录的ID
        /// </summary>
        /// <param name="imgInfo"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateZKImgInfo(ZK_IMAGE_TBL imgInfo,int id)
        {
            string strSql = "UPDATE dbo.ZL_TBL ";

            strSql += "SET ZL_FILE_ID='" + imgInfo.O_ZL_FILE_ID + "',";
            strSql += "ZK_CODE='" + imgInfo.O_ZK_CODE + "',";
            strSql += "YX_LENGTH='" + imgInfo.O_YX_LENGTH.ToString() + "',";
            strSql += "START_JD='" + imgInfo.O_START_JD.ToString() + "',";
            strSql += "END_JD='" + imgInfo.O_END_JD.ToString() + "',";
            strSql += " SUM_HC='" + imgInfo.O_SUM_HC.ToString() + "',";
            strSql += " HC='" + imgInfo.O_HC.ToString() + "',";
            strSql += " HC_START_DEEP='" + imgInfo.O_HC_START_DEEP.ToString() + "',";
            strSql += " HC_END_DEEP='" + imgInfo.O_HC_END_DEEP.ToString() + "',";
            strSql += " PS_NUM='" + imgInfo.O_PS_NUM.ToString() + "',";
            strSql += " PIC_FILE_NAME='" + imgInfo.O_PIC_FILE_NAME + "',";
            strSql += " PIC_START_DEEP='" + imgInfo.O_PIC_START_DEEP.ToString() + "',";
            strSql += " PIC_END_DEEP='" + imgInfo.O_PIC_END_DEEP.ToString() + "',";
            strSql += " BMP_FILE_NAME='" + imgInfo.O_BMP_FILE_NAME + "' ";

            strSql += "WHERE ID='" + id + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }
    }
}
